const router = require('koa-router')()
var svgCaptcha = require('svg-captcha');
const jwt = require("jsonwebtoken")
const mykey = "djxdjxdjx"
const formidable = require("formidable")
const path = require('path')
const fs = require('fs')
const db = require('../db/dbmanager')
const dirPath = path.join(path.resolve(__dirname, '../'), '/public/images')

router.get('/', async (ctx, next) => {
  await ctx.render('index', {
    title: 'Hello Koa 2!'
  })
})

router.post("/captcha", async (ctx, next) => {
  var captcha = svgCaptcha.create();
  ctx.session.captcha = captcha.text;
  ctx.body = captcha.data;
})

// 后台登录
router.post("/login", async (ctx, next) => {
  let user = ctx.request.body
  let { managerName, password } = user;
  console.log(user);
  let message = {}
  let sql = 'select * from manager where managerName=? and password=?'
  let result = ctx.db.EXCUTE(sql, [user.managerName, user.password]);
  console.log(result);
  if (result.length == 0) {
    message = { code: 400 }
  } else {
    const token = jwt.sign(user, mykey)
    message = { code: 200, token: token, success: true }
  }
  ctx.body = message;
})

// 后台注册
router.post('/register', async (ctx, next) => {
  let user = ctx.request.body
  // console.log(user);
  let sql1 = 'select * from manager where managerName = ?'
  let sql2 = 'insert into manager (managerName, password) value (?, ?)'
  let result = await ctx.db.EXCUTE(sql1, [user.managerName])
  console.log(result);
  if (result.length > 0) {
    message = { code: 400 }
  }
  else {
    await ctx.db.EXCUTE(sql2, [user.managerName, user.password])
    message = { code: 200 }
  }
  ctx.body = message;
})

// 后台：查询显示用户信息
router.post('/putuser', async (ctx, next) => {
  let sql = 'select * from users'
  let usersList = await ctx.db.EXCUTE(sql);
  ctx.body = { List: usersList };
})

// 后台：增加用户信息
router.post('/adduser', async (ctx, next) => {
  let user = ctx.request.body
  let { userName, password, sex, birthday, avatarUrl } = user
  console.log(user);
  let sql1 = 'select * from users where userName = ?'
  let sql2 = 'insert into users (userName, password, sex, birthday,avatarUrl) value (?,?,?,?,?)'
  let result = await ctx.db.EXCUTE(sql1, [userName])
  console.log(result);
  if (result.length > 0) {
    message = { code: 400 }
  }
  else {
    await ctx.db.EXCUTE(sql2, [userName, password, sex, birthday, avatarUrl])
    message = { code: 200 }
  }
  ctx.body = message;
})

// 后台：删除用户信息
router.post('/deluser', async (ctx, next) => {
  let user = ctx.request.body
  console.log(user);
  let { userName } = user;
  let message = {}
  let sql1 = 'select * from users where userName=?'
  let sql = 'delete  from users where userName=?'
  let result = await ctx.db.EXCUTE(sql1, [userName])
  console.log(result);
  if (result.length = 0) {
    message = { code: 401 }
  } else {
    await ctx.db.EXCUTE(sql, [userName], async (err, data) => {
      console.log(data);
      if (err) {
        message = { code: 400 }
      }
      if (data.affectedRows > 0) {
        message = { code: 200 }
      }
      else { message = { code: 202 } }
    })
  }
  ctx.body = message
})

// 后台：修改用户信息
router.post('/edituser', async (ctx, next) => {
  let user = ctx.request.body
  let { userId, userName, password, sex, birthday, avatarUrl } = user
  console.log(user);
  let sql1 = 'select * from users where userId = ?'
  let sql2 = 'update users set userName=?, password=?,sex=?,birthday=?,avatarUrl=? where userId=?'
  let result = await ctx.db.EXCUTE(sql1, [userId])
  console.log(result);
  if (result.length == 0) {
    message = { code: 400 }
  }
  else {
    let results = await ctx.db.EXCUTE(sql2, [userName, password, sex, birthday, avatarUrl, userId])
    message = { code: 200, List: results }
    console.log(results)
  }
  ctx.body = message;
})

// 后台：查询准确用户信息
router.post('/seluser', async (ctx, next) => {
  let user = ctx.request.body
  let { userName, password, sex, birthday, avatarUrl } = user
  console.log(user);
  let sql = 'select * from users where userName = ?'
  let result = await ctx.db.EXCUTE(sql, [userName, password, sex, birthday, avatarUrl])
  console.log(result);
  if (result.length == 0) {
    message = { code: 400 }
  }
  else {
    message = { code: 200, List: result }
  }
  ctx.body = message;
})

// 后台：获取用户分页
router.get('/getUserData', (req, res) => {
  const sqlStr = `SELECT * FROM users`;
  connection.query(sqlStr, (error, results, fields) => {
    if (error) {
      res.json({ err_code: 0, message: "获取失败" });
    } else {
      results = JSON.parse(JSON.stringify(results));
      res.json({ success_code: 200, data: results })
    }
  })
})

// 后台：用户分页查询
router.post('/pageuser', (req, res) => {
  let pageNo = Number(req.body.page) || 1;
  let pageCount = Number(req.body.pageSize) || 10;
  let pageSize = pageCount;
  let page = (pageNo - 1) * pageCount;
  console.log(page, pageSize);
  let sqlStr = `select * from users limit ${page},${pageSize}`;
  connection.query(sqlStr, (error, results, fields) => {
    if (error) {
      console.log(error);
      res.json({ err_code: 0, message: "获取分页数据失败" });
    } else {
      console.log(results);
      res.json({ success_code: 200, message: '获取分页数据成功', data: results })
    }
  })
})

// 后台：获取需求列表分页
router.get('/getRequestData', (req, res) => {
  const sqlStr = `SELECT * FROM request`;
  connection.query(sqlStr, (error, results, fields) => {
    if (error) {
      res.json({ err_code: 0, message: "获取失败" });
    } else {
      results = JSON.parse(JSON.stringify(results));
      res.json({ success_code: 200, data: results })
    }
  })
})

// 后台：需求列表分页查询
router.post('/pageRequest', (req, res) => {
  let pageNo = Number(req.body.page) || 1;
  let pageCount = Number(req.body.pageSize) || 10;
  let pageSize = pageCount;
  let page = (pageNo - 1) * pageCount;
  console.log(page, pageSize);
  let sqlStr = `select * from request limit ${page},${pageSize}`;
  connection.query(sqlStr, (error, results, fields) => {
    if (error) {
      console.log(error);
      res.json({ err_code: 0, message: "获取分页数据失败" });
    } else {
      console.log(results);
      res.json({ success_code: 200, message: '获取分页数据成功', data: results })
    }
  })
})

// 后台：查询显示需求列表信息
router.post('/putRequest', async (ctx, next) => {
  let sql = 'select * from request'
  let requestList = await ctx.db.EXCUTE(sql);
  ctx.body = { List: requestList };
})

// 后台：增加需求列表信息
router.post('/addRequest', async (ctx, next) => {
  let request = ctx.request.body
  let { requestId, avatarUrl, userName, requestDate, requestText, requestImage } = request
  console.log(request);
  let sql1 = 'select * from request where requestId = ?'
  let sql2 = 'insert into request (requestId,avatarUrl,userName,requestDate,requestText,requestImage) value (?,?,?,?,?,?)'
  let result = await ctx.db.EXCUTE(sql1, [requestId])
  console.log(result);
  if (result.length > 0) {
    message = { code: 400 }
  }
  else {
    await ctx.db.EXCUTE(sql2, [requestId, avatarUrl, userName, requestDate, requestText, requestImage])
    message = { code: 200 }
  }
  ctx.body = message;
})

// 后台：删除需求列表信息
router.post('/delRequest', async (ctx, next) => {
  let request = ctx.request.body
  console.log(request);
  let { requestId } = request;
  let message = {}
  let sql1 = 'select * from request where requestId=?'
  let sql = 'delete from request where requestId=?'
  let result = await ctx.db.EXCUTE(sql1, [requestId])
  console.log(result);
  if (result.length = 0) {
    message = { code: 401 }
  } else {
    await ctx.db.EXCUTE(sql, [requestId], async (err, data) => {
      console.log(data);
      if (err) {
        message = { code: 400 }
      }
      if (data.affectedRows > 0) {
        message = { code: 200 }
      }
      else { message = { code: 202 } }
    })
  }
  ctx.body = message
})

// 后台：修改需求列表信息
router.post('/editRequest', async (ctx, next) => {
  let request = ctx.request.body
  let { requestId, avatarUrl, userName, requestDate, requestText, requestImage } = request
  console.log(request);
  let sql1 = 'select * from request where requestId = ?'
  let sql2 = 'update request set userName=?, avatarUrl=?,requestDate=?,requestText=?,requestImage=? where requestId=?'
  let result = await ctx.db.EXCUTE(sql1, [requestId])
  console.log(result);
  if (result.length == 0) {
    message = { code: 400 }
  }
  else {
    let results = await ctx.db.EXCUTE(sql2, [userName, avatarUrl, requestDate, requestText, requestImage, requestId])
    message = { code: 200, List: results }
    console.log(results)
  }
  ctx.body = message;
})

// 后台：查询准确需求列表信息
router.post('/selRequest', async (ctx, next) => {
  let request = ctx.request.body
  let { userName } = request
  console.log(request);
  let sql = 'select * from request where userName = ?'
  let result = await ctx.db.EXCUTE(sql, [userName])
  console.log(result);
  if (result.length == 0) {
    message = { code: 400 }
  }
  else {
    message = { code: 200, List: result }
  }
  ctx.body = message;
})

// 后台：获取出租物品分页
router.get('/getSaleData', (req, res) => {
  const sqlStr = `SELECT * FROM sale`;
  connection.query(sqlStr, (error, results, fields) => {
    if (error) {
      res.json({ err_code: 0, message: "获取失败" });
    } else {
      results = JSON.parse(JSON.stringify(results));
      res.json({ success_code: 200, data: results })
    }
  })
})

// 后台：出租物品分页查询
router.post('/pageSale', (req, res) => {
  let pageNo = Number(req.body.page) || 1;
  let pageCount = Number(req.body.pageSize) || 10;
  let pageSize = pageCount;
  let page = (pageNo - 1) * pageCount;
  console.log(page, pageSize);
  let sqlStr = `select * from sale limit ${page},${pageSize}`;
  connection.query(sqlStr, (error, results, fields) => {
    if (error) {
      console.log(error);
      res.json({ err_code: 0, message: "获取分页数据失败" });
    } else {
      console.log(results);
      res.json({ success_code: 200, message: '获取分页数据成功', data: results })
    }
  })
})

// 后台：查询显示出租物品信息
router.post('/putSale', async (ctx, next) => {
  let sql = 'select * from sale'
  let saleList = await ctx.db.EXCUTE(sql);
  ctx.body = { List: saleList };
})

// 后台：增加出租物品信息
router.post('/addSale', async (ctx, next) => {
  let sale = ctx.request.body
  let { saleId, avatarUrl, userName, saleDate, saleText, saleImage } = sale
  console.log(sale);
  let sql1 = 'select * from sale where saleId = ?'
  let sql2 = 'insert into sale (saleId,avatarUrl,userName,saleDate,saleText,saleImage) value (?,?,?,?,?,?)'
  let result = await ctx.db.EXCUTE(sql1, [saleId])
  console.log(result);
  if (result.length > 0) {
    message = { code: 400 }
  }
  else {
    await ctx.db.EXCUTE(sql2, [saleId, avatarUrl, userName, saleDate, saleText, saleImage])
    message = { code: 200 }
  }
  ctx.body = message;
})

// 后台：删除出租物品信息
router.post('/delSale', async (ctx, next) => {
  let sale = ctx.request.body
  console.log(sale);
  let { saleId } = sale;
  let message = {}
  let sql1 = 'select * from sale where saleId=?'
  let sql = 'delete from sale where saleId=?'
  let result = await ctx.db.EXCUTE(sql1, [saleId])
  console.log(result);
  if (result.length = 0) {
    message = { code: 401 }
  } else {
    await ctx.db.EXCUTE(sql, [saleId], async (err, data) => {
      console.log(data);
      if (err) {
        message = { code: 400 }
      }
      if (data.affectedRows > 0) {
        message = { code: 200 }
      }
      else { message = { code: 202 } }
    })
  }
  ctx.body = message
})

// 后台：修改出租物品信息
router.post('/editSale', async (ctx, next) => {
  let sale = ctx.request.body
  let { saleId, avatarUrl, userName, saleDate, saleText, saleImage } = sale
  console.log(sale);
  let sql1 = 'select * from sale where saleId = ?'
  let sql2 = 'update sale set userName=?, avatarUrl=?,saleDate=?,saleText=?,saleImage=? where saleId=?'
  let result = await ctx.db.EXCUTE(sql1, [saleId])
  console.log(result);
  if (result.length == 0) {
    message = { code: 400 }
  }
  else {
    let results = await ctx.db.EXCUTE(sql2, [saleId, avatarUrl, userName, saleDate, saleText, saleImage])
    message = { code: 200, List: results }
    console.log(results)
  }
  ctx.body = message;
})

// 后台：查询准确出租物品信息
router.post('/selSale', async (ctx, next) => {
  let sale = ctx.request.body
  let { userName } = sale
  console.log(sale);
  let sql = 'select * from sale where userName = ?'
  let result = await ctx.db.EXCUTE(sql, [userName])
  console.log(result);
  if (result.length == 0) {
    message = { code: 400 }
  }
  else {
    message = { code: 200, List: result }
  }
  ctx.body = message;
})

// 后台：获取地址信息分页
router.get('/getAddressData', (req, res) => {
  const sqlStr = `SELECT * FROM address`;
  connection.query(sqlStr, (error, results, fields) => {
    if (error) {
      res.json({ err_code: 0, message: "获取失败" });
    } else {
      results = JSON.parse(JSON.stringify(results));
      res.json({ success_code: 200, data: results })
    }
  })
})

// 后台：地址信息分页查询
router.post('/pageAddress', (req, res) => {
  let pageNo = Number(req.body.page) || 1;
  let pageCount = Number(req.body.pageSize) || 10;
  let pageSize = pageCount;
  let page = (pageNo - 1) * pageCount;
  console.log(page, pageSize);
  let sqlStr = `select * from address limit ${page},${pageSize}`;
  connection.query(sqlStr, (error, results, fields) => {
    if (error) {
      console.log(error);
      res.json({ err_code: 0, message: "获取分页数据失败" });
    } else {
      console.log(results);
      res.json({ success_code: 200, message: '获取分页数据成功', data: results })
    }
  })
})

// 后台：查询显示地址信息信息
router.post('/putAddress', async (ctx, next) => {
  let sql = 'select * from address'
  let addressList = await ctx.db.EXCUTE(sql);
  ctx.body = { List: addressList };
})

// 后台：查询准确地址信息
router.post('/selAddress', async (ctx, next) => {
  let address = ctx.request.body
  let { userName } = address
  console.log(address);
  let sql = 'select * from address where userName = ?'
  let result = await ctx.db.EXCUTE(sql, [userName])
  console.log(result);
  if (result.length == 0) {
    message = { code: 400 }
  }
  else {
    message = { code: 200, List: result }
  }
  ctx.body = message;
})

// 后台：增加地址信息
router.post('/addAddress', async (ctx, next) => {
  let address = ctx.request.body
  let { userName, addressName, phoneNum, park, building } = JSON.parse(address)
  console.log(address);
  let sql1 = 'select * from address where userName = ?'
  let sql2 = 'insert into address (userName,addressName,phoneNum,park,building) value (?,?,?,?,?)'
  let result = await ctx.db.EXCUTE(sql1, [userName])
  console.log(result);
  // if (result.length > 0) {
  //   message = { code: 400 }
  // }
  // else {
  await ctx.db.EXCUTE(sql2, [userName, addressName, phoneNum, park, building])
  message = { code: 200 }
  // }
  ctx.body = message;
})

// 后台：删除地址信息
router.post('/delAddress', async (ctx, next) => {
  let address = ctx.request.body
  console.log(address);
  let { addressId } = address;
  let message = {}
  let sql1 = 'select * from address where addressId=?'
  let sql = 'delete from address where addressId=?'
  let result = await ctx.db.EXCUTE(sql1, [addressId])
  console.log(result);
  if (result.length = 0) {
    message = { code: 401 }
  } else {
    await ctx.db.EXCUTE(sql, [addressId], async (err, data) => {
      console.log(data);
      if (err) {
        message = { code: 400 }
      }
      if (data.affectedRows > 0) {
        message = { code: 200 }
      }
      else { message = { code: 202 } }
    })
  }
  ctx.body = message
})

// 后台：修改地址信息
router.post('/editAddress', async (ctx, next) => {
  let address = ctx.request.body
  let { userName, addressName, phoneNum, park, building, addressId } = address
  console.log(address);
  let sql1 = 'select * from address where addressId = ?'
  let sql2 = 'update address set userName=?,addressName=?, phoneNum=?,park=?,building=? where addressId=?'
  let result = await ctx.db.EXCUTE(sql1, [saleId])
  console.log(result);
  if (result.length == 0) {
    message = { code: 400 }
  }
  else {
    let results = await ctx.db.EXCUTE(sql2, [userName, addressName, phoneNum, park, building, addressId])
    message = { code: 200, List: results }
    console.log(results)
  }
  ctx.body = message;
})

// 后台：获取评论信息分页
router.get('/getPinglunData', (req, res) => {
  const sqlStr = `SELECT * FROM pinglun`;
  connection.query(sqlStr, (error, results, fields) => {
    if (error) {
      res.json({ err_code: 0, message: "获取失败" });
    } else {
      results = JSON.parse(JSON.stringify(results));
      res.json({ success_code: 200, data: results })
    }
  })
})

// 后台：评论信息分页查询
router.post('/pagePinglun', (req, res) => {
  let pageNo = Number(req.body.page) || 1;
  let pageCount = Number(req.body.pageSize) || 10;
  let pageSize = pageCount;
  let page = (pageNo - 1) * pageCount;
  console.log(page, pageSize);
  let sqlStr = `select * from pinglun limit ${page},${pageSize}`;
  connection.query(sqlStr, (error, results, fields) => {
    if (error) {
      console.log(error);
      res.json({ err_code: 0, message: "获取分页数据失败" });
    } else {
      console.log(results);
      res.json({ success_code: 200, message: '获取分页数据成功', data: results })
    }
  })
})

// 后台：查询显示评论信息信息
router.post('/putPinglun', async (ctx, next) => {
  let sql = 'select * from pinglun'
  let pinglunList = await ctx.db.EXCUTE(sql);
  ctx.body = { List: pinglunList };
})

// 后台：查询准确评论信息
router.post('/selPinglun', async (ctx, next) => {
  let pinglun = ctx.request.body
  let { userName } = pinglun
  console.log(pinglun);
  let sql = 'select * from pinglun where userName = ?'
  let result = await ctx.db.EXCUTE(sql, [userName])
  console.log(result);
  if (result.length == 0) {
    message = { code: 400 }
  }
  else {
    message = { code: 200, List: result }
  }
  ctx.body = message;
})

// 后台：增加评论信息
router.post('/addPinglun', async (ctx, next) => {
  let pinglun1 = ctx.request.body
  let { userName, pinglunName, pinglunDate, pinglun } = pinglun1
  console.log(pinglun1);
  let sql1 = 'select * from pinglun where userName = ?'
  let sql2 = 'insert into pinglun (userName,pinglunName, pinglunDate, pinglun) value (?,?,?,?)'
  let result = await ctx.db.EXCUTE(sql1, [userName])
  console.log(result);
  if (result.length > 0) {
    message = { code: 400 }
  }
  else {
    await ctx.db.EXCUTE(sql2, [userName, pinglunName, pinglunDate, pinglun])
    message = { code: 200 }
  }
  ctx.body = message;
})

// 后台：删除评论信息
router.post('/delPinglun', async (ctx, next) => {
  let pinglun = ctx.request.body
  console.log(pinglun);
  let { userName, pinglunName } = pinglun;
  let message = {}
  let sql1 = 'select * from pinglun where userName=? and pinglunName=?'
  let sql = 'delete from pinglun where userName=? and pinglunName=?'
  let result = await ctx.db.EXCUTE(sql1, [userName, pinglunName])
  console.log(result);
  if (result.length = 0) {
    message = { code: 401 }
  } else {
    await ctx.db.EXCUTE(sql, [userName, pinglunName], async (err, data) => {
      console.log(data);
      if (err) {
        message = { code: 400 }
      }
      if (data.affectedRows > 0) {
        message = { code: 200 }
      }
      else { message = { code: 202 } }
    })
  }
  ctx.body = message
})

// 后台：修改评论信息
router.post('/editPinglun', async (ctx, next) => {
  let pinglun1 = ctx.request.body
  let { userName, pinglunName, pingDate, pinglun, pinglunId } = pinglun1
  console.log(pinglun1);
  let sql1 = 'select * from pinglun where pinglunId = ?'
  let sql2 = 'update pinglun set userName=?,pinglunName=?, pinglunDate=?,pinglun=? where pinglunId=?'
  let result = await ctx.db.EXCUTE(sql1, [pinglunId])
  console.log(result);
  if (result.length == 0) {
    message = { code: 400 }
  }
  else {
    let results = await ctx.db.EXCUTE(sql2, [userName, pinglunName, pingDate, pinglun, pinglunId])
    message = { code: 200, List: results }
    console.log(results)
  }
  ctx.body = message;
})


// 前端：显现所有地址信息
router.post('/DeliveryAddress', async (ctx, next) => {
  let sql = 'select * from address'
  let pinglunList = await ctx.db.EXCUTE(sql);
  console.log(pinglunList)
  ctx.body = { List: pinglunList };
})

// 前端：增加地址信息
router.post('/befoAddAddress', async (ctx, next) => {
  let address = ctx.request.body
  let { userName1, addressName, phoneNum, park, building } = JSON.parse(address)
  let sql1 = 'select * from address where userName=? '
  let sql = 'insert into address (userName, addressName,phoneNum,park,building) value (?,?,?,?,?)'
  let result1 = await ctx.db.EXCUTE(sql1, [userName1])
  console.log(result1);
  if (result1.length == 0) {
    message = { code: 400 }
  }
  else {
    console.log(userName1, addressName, phoneNum, park, building);
    await ctx.db.EXCUTE(sql, [userName1, addressName, phoneNum, park, building])
    message = { code: 200 }
  }
  ctx.body = message;
})

// 显示出租信息
router.post('/fputsale', async (ctx, next) => {
  let text = [];
  let result = []
  let sql = 'select * from sale'
  // 查找照片的时候就要匹配上对应的text---已完成
  // 现在需要解决的就是怎么让sale库里的东西和img库里的照片匹配----已完成
  // 我往img中传入了text，我怎么把sql1查询后得到的结果插入到resulSale对象数组的每一个对象中
  let message = {}
  let resultSale = await ctx.db.EXCUTE(sql)
  for (var i = 0; i <resultSale.length ; i++) {
    // console.log(resultSale.length);
    text[i] = resultSale[i].saleText
    // console.log(text[i]);
    // 查到全部信息后再和resultSale里的text对比，如果text相同就把imgUrl属性插入
    let sql1 = 'select * from saleimg where text=?'
    let resultSale1 = await ctx.db.EXCUTE(sql1, [text[i]])
    // console.log(resultSale1[0].text);
    // console.log(resultSale[i].saleText);
    if (resultSale1[0].text == resultSale[i].saleText) {
      let sql3 = 'update sale set saleImage =? where saleText=? '
      await ctx.db.EXCUTE(sql3, [resultSale1[0].imgUrl,resultSale1[0].text])
      let sql4 = 'select * from sale'
      result = await ctx.db.EXCUTE(sql4)
      console.log(result);
    }
  }
  if (result) {
    message = { code: 200, msg: "成功", List: result }
  }
  else {
    message = { code: 400, mgs: '失败', List: result }
  }
  ctx.body = message
  // ctx.body='putsale'
  // 把照片的数组和出租信息的数组拼接到一个新的数组里面
  // resultSale=[...resultSale,...all]
  // console.log(resultSale);
  // resultSale[i]['saleImage']=resultSale1[i].imgUrl;
  // all=[...resultSale]
  // console.log(all);
  // console.log(resultSale1);
  // all.push(resultSale1)
})

// 接受存储评论（包括评论内容，以及用户信息和被评论作者信息）
router.post('/pinglun', async (ctx, next) => {
  let info = JSON.parse(ctx.request.body)
  console.log(info);
  let timer = new Date;
  var hour = timer.getHours(); //获取当前小时
  var minute = timer.getMinutes(); //获取当前分钟
  var second = timer.getSeconds(); //获取当前秒
  let hours = hour < 10 ? "0" + hour : hour
  let minutes = minute < 10 ? "0" + minute : minute
  let seconds = second < 10 ? "0" + second : second
  let Date2 = hours + ":" + minutes + ":" + seconds
  console.log(Date2);
  // 如果评论内容为空就不存储
  if (info.text) {
    let sql = 'insert into pinglun (userName, pinglunName,pinglunDate,pinglun) value (?,?,?,?)'
    await ctx.db.EXCUTE(sql, [info.userName, info.pinglunName, Date2, info.text]);
  }
  let sql1 = 'select * from pinglun where userName=?'
  let pinglunList = await ctx.db.EXCUTE(sql1, [info.userName])
  console.log(pinglunList);
  ctx.body = { List: pinglunList };
})


// 显示评论
router.post('/fputpinglun', async (ctx, next) => {
  let info = JSON.parse(ctx.request.body)
  let sql1 = 'select * from pinglun where userName=?and pinglunName=?'
  let pinglunList = await ctx.db.EXCUTE(sql1, [info.userName, info.pinglunName])
  console.log(pinglunList);
  ctx.body = { List: pinglunList };
})

// 前端：修改用户信息
router.post('/Etiduser', async (ctx, next) => {
  let message = {};
  let user = JSON.parse(ctx.request.body)
  let { userName1, userName, password, sex, birthday } = user
  console.log(userName1);
  let sql1 = 'select userId from users where userName = ?'
  let sql2 = 'update users set userName=?, password=?,sex=?,birthday=? where userId=?'
  let result1 = await ctx.db.EXCUTE(sql1, [userName1])
  console.log(result1[0].userId);
  if (result1.length == 0) {
    message = { code: 400 }
  }
  else {
    let results = await ctx.db.EXCUTE(sql2, [userName, password, sex, birthday, result1[0].userId])
    let sql3 = 'select * from users where userId = ?'
    let result3 = await ctx.db.EXCUTE(sql3, [result1[0].userId])
    message = { code: 200, List: result3 }
    console.log(results)
  }
  ctx.body = message;
})

// 前端：注册
router.post("/qianregister", async (ctx, next) => {
  let user = ctx.request.body
  // console.log(user);
  let sql1 = 'select * from users where userName = ?'
  let sql2 = 'insert into users (userName, password) value (?, ?)'
  let result = await ctx.db.EXCUTE(sql1, [user.userName])
  console.log(result);
  if (result.length > 0) {
    message = { code: 400 }
  }
  else {
    await ctx.db.EXCUTE(sql2, [user.userName, user.password])
    message = { code: 200 }
  }
  ctx.body = message;
})

// 前端：登录
router.post("/qianlogin", async (ctx, next) => {
  console.log(ctx.request.body);
  let sql = 'select * from users where userName=? and password=?';
  let res = await ctx.db.EXCUTE(sql, [ctx.request.body.userName, ctx.request.body.password]);
  if (res.length > 0) {
    ctx.body = { code: 200, message: res }
  } else {
    ctx.body = { code: 400, message: "fail" }
  }
})

// 前端：忘记密码
router.post("/forget", async (ctx, next) => {
  console.log(ctx.request.body);
  let sql = 'select password from users where userName=? and birthday=?';
  let res = await ctx.db.EXCUTE(sql, [ctx.request.body.userName, ctx.request.body.birthday]);
  if (res.length > 0) {
    console.log(res);
    console.log(res.password);
    ctx.body = { code: 200, message: res }
  } else {
    ctx.body = { code: 400, message: "fail" }
  }
})

//前端：删除地址
router.post('/qiandelAddress', async (ctx, next) => {
  let id = JSON.parse(ctx.request.body);
  let sql1 = 'delete from address where addressId=?';
  let res1 = await ctx.db.EXCUTE(sql1, [id.addressId])
  console.log(res1);
  if (res1.affectedRows != 0) {
    let sql2 = 'select * from address'
    let res2 = await ctx.db.EXCUTE(sql2)
    ctx.body = { code: 200, List: res2 }
  } else {
    ctx.body = { code: 400 }
  }
})

// 前端：显现所有合同信息
router.post('/Contract', async (ctx, next) => {
  let sql = 'select * from contract'
  let List = await ctx.db.EXCUTE(sql);
  console.log(List)
  ctx.body = { List: List };
})

// 前端：显现所有退还押金信息
router.post('/DepositRefund', async (ctx, next) => {
  let sql = 'select * from cash'
  let List = await ctx.db.EXCUTE(sql);
  console.log(List)
  ctx.body = { List: List };
})

//前端：退还押金
router.post('/qiandelCash', async (ctx, next) => {
  let id = JSON.parse(ctx.request.body);
  let sql1 = 'delete from cash where cashId=?';
  let res1 = await ctx.db.EXCUTE(sql1, [id.cashId])
  console.log(res1);
  if (res1.affectedRows != 0) {
    let sql2 = 'select * from cash'
    let res2 = await ctx.db.EXCUTE(sql2)
    ctx.body = { code: 200, List: res2 }
  } else {
    ctx.body = { code: 400 }
  }
})

// 前端：显现所有需求列表
router.post('/puMyrequest', async (ctx, next) => {
  let sql = 'select * from request'
  let List = await ctx.db.EXCUTE(sql);
  console.log(List)
  ctx.body = { List: List };
})

// 前端：显现所有出租物品
router.post('/puMysale', async (ctx, next) => {
  let sql = 'select * from sale'
  let List = await ctx.db.EXCUTE(sql);
  console.log(List)
  ctx.body = { List: List };
})

// 前端：显现所有聊天
router.post('/chat', async (ctx, next) => {
  let user = JSON.parse(ctx.request.body)
  let { userName1, AuserName, userName } = user
  console.log(userName1);
  let sql1 = 'select * from chat where userName = ?'
  let result1 = await ctx.db.EXCUTE(sql1, [userName1])
  ctx.body = { List: result1 };
})

// 前端：增加对话
router.post('/addlist', async (ctx, next) => {
  let list = ctx.request.body
  let { AuserName, BuserName, name, message } = JSON.parse(list)
  let sql = 'insert into userChat (AuserName,BuserName,name,message) value (?,?,?,?)'
  console.log(list);
  console.log(AuserName, BuserName, name, message);
  await ctx.db.EXCUTE(sql, [AuserName, BuserName, name, message])
  message = { code: 200 }
  ctx.body = message;
})

// 与卖家聊天向数据库插入聊天人信息
router.post('/fchat', async (ctx, next) => {
  let info = JSON.parse(ctx.request.body)
  let message = {}
  let sql1 = 'select * from chat where userName=?and AuserName=?'
  let userList = await ctx.db.EXCUTE(sql1, [info.userName, info.AuserName])
  // console.log(userList);
  if (userList == []) {
    let sql = 'insert into chat (userName, AuserName) value (?,?)'
    await ctx.db.EXCUTE(sql, [info.userName, info.AuserName])
    message = { code: 200 };
  }
  else {
    message = { code: 400 }
  }
  ctx.body = message;
})

// 前端：显示对话
router.post('/list', async (ctx, next) => {
  let user = JSON.parse(ctx.request.body)
  let { AuserName, BuserName } = user
  console.log(AuserName, BuserName);
  let sql1 = 'select * from userChat where AuserName = ? and BuserName=?'
  let result1 = await ctx.db.EXCUTE(sql1, [AuserName, BuserName])
  let sql2 = 'select * from userChat where AuserName = ? and BuserName=?'
  let result2 = await ctx.db.EXCUTE(sql2, [BuserName, AuserName])
  result1 = [...result1, ...result2]
  ctx.body = { List: result1 };
})

// 前端：修改地址信息
router.post('/befoChangeAddress', async (ctx, next) => {
  let message = {};
  let user = JSON.parse(ctx.request.body)
  let { userName1, addressName, phoneNum, park, building, addressId } = user
  console.log(userName1);
  let sql1 = 'select addressId from address where userName = ?'
  let sql2 = 'update address set addressName=?, phoneNum=?,park=?,building=? where addressId=?'
  let result1 = await ctx.db.EXCUTE(sql1, [userName1])
  console.log(result1[0].addressId);
  if (result1.length == 0) {
    message = { code: 400 }
  }
  else {
    let results = await ctx.db.EXCUTE(sql2, [addressName, phoneNum, park, building, result1[0].addressId])
    let sql3 = 'select * from users where userId = ?'
    let result3 = await ctx.db.EXCUTE(sql3, [result1[0].addressId])
    message = { code: 200, List: result3 }
    console.log(results)
  }
  ctx.body = message;
})

// 图片上传
const uploadImg = (ctx) => {
  const form = formidable({
    multiples: true,
    uploadDir: dirPath,
    keepExtensions: true,
    allowEmptyFiles: false
  });

  return new Promise((resolve, reject) => {
    form.parse(ctx.req, (err, fields, files) => {
      //ctx.req原生的node的http请求模块；ctx.require.body是经过koa二次封装之后的
      if (err) {
        reject(err);
        return;
      }
      let keys = Object.keys(files);//获取files对象中的属性名[formData传入的图片]
      keys?.map((key) => {
        console.log(key);
        // 将文件重命名为原本的文件名
        // fs.rename(path.join(dirPath, `/${files[key].newFilename}`), path.join(dirPath, `/${files[key].originalFilename}`),
        //   (err) => {
        //     if (err) {
        //       console.log(err);
        //     } else {
        //       console.log('file is reset!');
        //     }
        //   })
      })
      resolve({ ok: true, fields, files });
    });
  });
}

// 提交需求
router.post('/pull', async (ctx, next) => {
  // 接收发布物品的信息,将文字插入到数据库中
  let info = JSON.parse(ctx.request.body)
  console.log(info.text);
  let message = {};
  let timer = new Date;
  var hour = timer.getHours(); //获取当前小时
  var minute = timer.getMinutes(); //获取当前分钟
  var second = timer.getSeconds(); //获取当前秒
  // 明天找一个时间试一下
  let hours = hour < 10 ? "0" + hour : hour
  let minutes = minute < 10 ? "0" + minute : minute
  let seconds = second < 10 ? "0" + second : second
  let Date1 = hours + ":" + minutes + ":" + seconds
  console.log(Date1);
  // console.log(hours,minutes,seconds);
  let sql = 'insert into request (requestText,requestDate,userName) value (?, ?,?)'
  let result = await ctx.db.EXCUTE(sql, [info.text, Date1, info.name]);
  console.log(result);
  if (result.length > 0) {
    message = { code: 200, msg: "成功" }
  }
  else {
    message = { code: 400, mgs: '失败' }
  }
  ctx.body = message
})

// 显示需求列表
router.post('/request', async (ctx, next) => {
  // let info = JSON.parse(ctx.request.body)
  let sql = 'select * from request'
  let message = {}
  let text = [];
  let result = []
  let resultrequest = await ctx.db.EXCUTE(sql)
  for (var i = 0; i < resultrequest.length; i++) {
    // console.log(resultrequest.length);
    text[i] = resultrequest[i].requestText
    console.log(text[i]);
    let sql1 = 'select * from img where text=?'
    let resultrequest1 = await ctx.db.EXCUTE(sql1, [text[i]])
    console.log(resultrequest1);
    if (resultrequest1[0].text == resultrequest[i].requestText) {
      let sql3 = 'update request set requestImage =? where requestText=? '
      await ctx.db.EXCUTE(sql3, [resultrequest1[0].imgUrl, resultrequest1[0].text])
      let sql4 = 'select * from request'
      result = await ctx.db.EXCUTE(sql4)
      console.log(result);
    }
  }
  if (result) {
    message = { code: 200, msg: "成功", List: result }
  }
  else {
    message = { code: 400, mgs: '失败', List: result }
  }
  ctx.body = message
})

// 提交出租物品
router.post('/sale', async (ctx, next) => {
  let infoSale = JSON.parse(ctx.request.body)
  console.log(infoSale.text);
  let message = {};
  let Depositprice=30
  let timer = new Date;
  var hour = timer.getHours(); //获取当前小时
  var minute = timer.getMinutes(); //获取当前分钟
  var second = timer.getSeconds(); //获取当前秒
  let hours = hour < 10 ? "0" + hour : hour
  let minutes = minute < 10 ? "0" + minute : minute
  let seconds = second < 10 ? "0" + second : second
  let Date2 = hours + ":" + minutes + ":" + seconds
  console.log(Date2);
  let sql = 'insert into sale (saleText,saleDate,avatarUrl,Depositprice,userName,shopprice,name) value (?, ?,?,?,?,?,?)'
  let result = await ctx.db.EXCUTE(sql, [infoSale.text, Date2,infoSale.avatar,Depositprice,infoSale.userName,infoSale.price,infoSale.name]);
  console.log(result);
  if (result.length > 0) {
    message = { code: 200, msg: "成功" }
  }
  else {
    message = { code: 400, mgs: '失败' }
  }
  ctx.body = message
})

// 加入购物车
router.post('/addshop', async (ctx, next) => {
  let info=JSON.parse(ctx.request.body)
  console.log(info);
  // 通过前台传过来的userName在sale数据库中查找信息
  let sql="select * from sale where userName=?"
  let result=await ctx.db.EXCUTE(sql,[info.userName])
  console.log(result);
  let sql1="select * from shop where name=?and userName=?"
  let result1=await ctx.db.EXCUTE(sql1,[result.name,info.userName])
  console.log(result1);
  ctx.body = 'addshop'
})

// 校验
router.post('/userImage', async (ctx, next) => {
  ctx.body = await uploadImg(ctx);
})


router.get('/upload', async (ctx, next) => {
  let token = ctx.req.headers.authorization;
  let result = jwt.verify(token, mykey);
  console.log(result);
  ctx.body = await uploadImg(ctx);
})

router.get('/getpage', async (ctx, next) => {
  let page = fs.readdirSync(dirPath);
  ctx.body = page;
})

router.post('/verify', async (ctx, next) => {
  let body = JSON.parse(ctx.request.body);
  let name = body.name;
  let id = body.id;
  if (name && id) {
    let token = jwt.sign({ name: name }, mykey);
    ctx.body = { token: token }
  }
})

router.get('/string', async (ctx, next) => {
  ctx.body = 'koa2 string'
})

router.get('/json', async (ctx, next) => {
  ctx.body = {
    title: 'koa2 json'
  }
})

module.exports = router